﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Chequeo_de_Bolsas_Reembolse
{
    class DatosReembolse
    {
        public static OleDbConnection conectarExcel()
        {
            OleDbConnection con;
            try
            {

                String file = Application.StartupPath + @"\Base Reembolse\Base Reembolse.xlsx";
                con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 8.0;");

            }
            catch (Exception)
            {
                con = null;
                MessageBox.Show("Error de Conexión!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return con;
        }
        public static DataSet leerDatos()
        {
            DataSet ds = new DataSet();
            OleDbConnection con = conectarExcel();
            try
            {


                con.Open();
                OleDbDataAdapter adaptador = new OleDbDataAdapter("SELECT Material, Descripcion, Lote FROM [Consolidadas$]", con);
                adaptador.Fill(ds, "tablaConsolidadas");
                con.Close();
                con.Dispose();
            }
            catch (Exception)
            {
                ds = null;

            }
            finally
            {
                con.Close();
                con.Dispose();
            }

            return ds;
        }

        public static String obtenerDescripcion(String material, String lote)
        {
            DataSet ds = new DataSet();
            String descripcion = null;
            OleDbConnection con = conectarExcel();
            try
            {


                
                String selectSQL = "SELECT * FROM [Consolidadas$] WHERE Material = " + material + " AND Lote = '" + lote + "'";
                OleDbDataAdapter adaptador = new OleDbDataAdapter(selectSQL, con);
                con.Open();
                adaptador.Fill(ds, "tablaConsolidadas");
                DataRow campo = ds.Tables[0].Rows[0];
                descripcion = campo["Descripcion"].ToString();
                con.Close();
                con.Dispose();
                
            }
            catch (Exception)
            {
                descripcion = null;

            }
            finally
            {
                con.Close();
                con.Dispose();
            }

            return descripcion;
        }
    }
}
